#region Using directives
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using webapp4U.BOL;
#endregion


namespace webapp4U.DAL
{
	 public class OrderItemsDAL : BaseDAL
	 {
	     #region ObjIDataReader

	     /// <summary>
	     /// Returns a new OrderItems instance filled with the DataReader's current record data 
	     /// </summary>
	     private OrderItems GetOrderItemsFromReader(IDataReader dr)
	     {
		     OrderItems obj = new OrderItems();
		     obj.OrderItemID = (dr["OrderItemID"] is DBNull) ? Int32.MinValue :  (Int32)dr["OrderItemID"];
		     obj.AddedDate = (dr["AddedDate"] is DBNull) ? DateTime.MinValue :  (DateTime)dr["AddedDate"];
		     obj.AddedBy = (dr["AddedBy"] is DBNull) ? String.Empty :  (String)dr["AddedBy"];
		     obj.OrderID = (dr["OrderID"] is DBNull) ? Int32.MinValue :  (Int32)dr["OrderID"];
		     obj.ProductID = (dr["ProductID"] is DBNull) ? Int32.MinValue :  (Int32)dr["ProductID"];
		     obj.Title = (dr["Title"] is DBNull) ? String.Empty :  (String)dr["Title"];
		     obj.SKU = (dr["SKU"] is DBNull) ? String.Empty :  (String)dr["SKU"];
		     obj.UnitPrice = (dr["UnitPrice"] is DBNull) ? Decimal.MinValue :  (Decimal)dr["UnitPrice"];
		     obj.Quantity = (dr["Quantity"] is DBNull) ? Int32.MinValue :  (Int32)dr["Quantity"];
		     return obj;
	     }
	     #endregion

	     /// <summary>
	     /// Returns a collection of OrderItems with the data read from the input DataReader
	     /// </summary>
	     private List<OrderItems> GetOrderItemsCollectionFromReader(IDataReader dr)
	     {
		     List<OrderItems> obj = new List<OrderItems>();
		     while(dr.Read())
			     obj.Add(GetOrderItemsFromReader(dr));
		     return obj;
	     }

	     public int Insert(OrderItems obj)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_OrderItems_Insert", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@OrderItemID", SqlDbType.Int).Direction = ParameterDirection.Output;
		     myCommand.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = obj.AddedDate;
		     myCommand.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = obj.AddedBy;
		     myCommand.Parameters.Add("@OrderID", SqlDbType.Int).Value = obj.OrderID;
		     myCommand.Parameters.Add("@ProductID", SqlDbType.Int).Value = obj.ProductID;
		     myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = obj.Title;
		     myCommand.Parameters.Add("@SKU", SqlDbType.NVarChar).Value = obj.SKU;
		     myCommand.Parameters.Add("@UnitPrice", SqlDbType.Money).Value = obj.UnitPrice;
		     myCommand.Parameters.Add("@Quantity", SqlDbType.Int).Value = obj.Quantity;

		     int ret = myCommand.ExecuteNonQuery();
		     return (int)myCommand.Parameters["@OrderItemID"].Value; 
		     }
	     }

	     public bool Update(OrderItems obj)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_OrderItems_Update", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@OrderItemID", SqlDbType.Int).Value = obj.OrderItemID;
		     myCommand.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = obj.AddedDate;
		     myCommand.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = obj.AddedBy;
		     myCommand.Parameters.Add("@OrderID", SqlDbType.Int).Value = obj.OrderID;
		     myCommand.Parameters.Add("@ProductID", SqlDbType.Int).Value = obj.ProductID;
		     myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = obj.Title;
		     myCommand.Parameters.Add("@SKU", SqlDbType.NVarChar).Value = obj.SKU;
		     myCommand.Parameters.Add("@UnitPrice", SqlDbType.Money).Value = obj.UnitPrice;
		     myCommand.Parameters.Add("@Quantity", SqlDbType.Int).Value = obj.Quantity;

		     int ret = myCommand.ExecuteNonQuery();
		     return (ret == 1);
		     }
	     }

	     public bool Delete(Int32 OrderItemID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_OrderItems_Delete", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@OrderItemID", SqlDbType.Int).Value = OrderItemID;

		     int ret = myCommand.ExecuteNonQuery();
		     return (ret == 1);
		     }
	     }

	     /// <summary>
	     /// Retrieves the w4u_OrderItems with the specified ID
	     /// </summary>
	     public OrderItems GetOrderItemsByID(Int32 OrderItemID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_OrderItems_GetOrderItemsByID", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@OrderItemID", SqlDbType.Int).Value = OrderItemID;

		     SqlDataReader dr = myCommand.ExecuteReader();
		     return (dr.Read() ? GetOrderItemsFromReader(dr) : null);
		     }
	     }

	     //Get All OrderItems
	     public List<OrderItems> GetAllOrderItems(int PageIndex, int PageSize)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_OrderItems_GetAllOrderItems", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
		     myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
		     SqlDataReader dr = myCommand.ExecuteReader();
		     return GetOrderItemsCollectionFromReader(dr);
		     }
	     }

	     // Get Count All records of Table  OrderItems
	     public int GetCountOrderItems()
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_OrderItems_GetCountOrderItems", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     return (int)myCommand.ExecuteScalar();
		     }
	     }

	     // Get OrderItemsByOrderID
	     public List<OrderItems> GetOrderItemsByOrderID(Int32 OrderID,int PageIndex, int PageSize)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_OrderItems_GetOrderItemsByOrderID", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@OrderID", SqlDbType.Int).Value = OrderID;
		     myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
		     myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
		     SqlDataReader dr = myCommand.ExecuteReader();
		     return GetOrderItemsCollectionFromReader(dr);
		     }
	     }

	     // Get OrderItemsCountByOrderID
	     public int GetOrderItemsCountByOrderID(Int32 OrderID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_OrderItems_GetOrderItemsCountByOrderID", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@OrderID", SqlDbType.Int).Value = OrderID;
		     return (int)myCommand.ExecuteScalar();
		     }
	     }

	 }
}
